﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoFlatnessTest.Db
{
    public class DbHelp
    {
        /// <summary>
        /// 得到一个主键
        /// </summary>
        public static string ExplicitKey { get => Guid.NewGuid().ToString("N"); }

        /// <summary>
        /// 得到一个未打开的SqlServer连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetSqlServerConn()
        {
            SqlConnection sb = new SqlConnection();
            sb.ConnectionString = ConModel.Data.SqlServer;
            return sb;
        }

        public static SqlConnection GetSqlServerConn5()
        {
            SqlConnection sb = new SqlConnection();
            sb.ConnectionString = ConModel.Data.SqlServer5;
            return sb;
        }

        /// <summary>
        /// 得到一个未打开的SQLite连接
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection GetSQLiteConn()
        {
            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
            sb.DataSource = LocalFile.DbPath;

            SQLiteConnection con = new SQLiteConnection(sb.ToString());
            return con;
        }

        /// <summary>
        /// 得到一个未打开的SQLite连接（数据中心）
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection GetSQLiteDataCenterConn()
        {
            XiaoYan(LocalFile.DbRecordDir);

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
            sb.DataSource = LocalFile.DbRecordDir;

            SQLiteConnection con = new SQLiteConnection(sb.ToString());
            return con;
        }

        static void XiaoYan(string dbPath)
        {
            if (!File.Exists(dbPath))
                File.WriteAllBytes(dbPath, new byte[] { });

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
            sb.DataSource = dbPath;

            using (SQLiteConnection connection = new SQLiteConnection(sb.ToString()))
            {
                var cz = connection.QueryFirstOrDefault<int>(@"
SELECT Count(1) FROM sqlite_master WHERE type='table' AND name = 'TestRecord'");
                if (cz == 0)
                {
                    connection.Execute(@"
CREATE TABLE TestRecord(
    Id TEXT(32) NOT NULL  , --ID
    ProjectId TEXT(32)   , --项目Id
    ProjectName TEXT(255)   , --项目名称
    GuidId TEXT(32)   , --组ID(同个测试周期)
    WorkOrder TEXT(255)   , --工单
    Mode INT   , --模式(1标准2快速3校准4单板测试10录入)
    RowNum INT   , --行号(原点为右下)
    ColumnNum INT   , --列号(原点为右下)
    TestNum INT   , --编号(原点为右下)
    IsOk INT   , --通过(1通过)
    MiSimi DOUBLE(24,6)   , --扫描相似度
    MiMaxVal DOUBLE(24,6)   , --扫描最大值
    MiMinVal DOUBLE(24,6)   , --扫描最小值
    MiVal DOUBLE(24,6)   , --扫描差值
    MiInfo TEXT(255)   , --扫描原始信息
    MaInfo TEXT(255)   , --扫码原始信息
    IsSync INT   , --是否同步(1否)
    CreatedTime DATETIME   , --创建时间
    PRIMARY KEY (Id)
)  ; --测试记录");

                }

                cz = connection.QueryFirstOrDefault<int>(@"
SELECT Count(1) FROM sqlite_master WHERE type='table' AND name = 'DemaRecord'");
                if (cz == 0)
                {
                    connection.Execute(@"
CREATE TABLE DemaRecord(
    Id TEXT(32) NOT NULL  , --ID
    TestNum INT   , --编号(1-5)
    IsOk INT   , --通过(1通过)
    MiSimi DOUBLE(24,6)   , --扫描相似度
    MiMaxVal DOUBLE(24,6)   , --扫描最大值
    MiMinVal DOUBLE(24,6)   , --扫描最小值
    MiVal DOUBLE(24,6)   , --扫描差值
    MiInfo TEXT(255)   , --扫描原始信息
    IsSync INT   , --是否同步(1否)
    CreatedTime DATETIME   , --创建时间
    PRIMARY KEY (Id)
)  ; --标定测试记录");

                }


            }
        }

    }
}
